package org.jeecg.modules.gis.util;

import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.geotools.data.simple.SimpleFeatureSource;
import org.opengis.feature.Property;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.feature.type.AttributeDescriptor;
import org.locationtech.jts.geom.Geometry;

import java.util.ArrayList;
import java.util.List;

public class ShapefileTableBuilder {

    /**
     * 构建建表 SQL
     *
     * @param featureSource GeoTools 读取的 feature source
     * @param rawTableName  原始表名
     * @param dbType        数据库类型 mysql / postgresql / dm
     * @return 建表 SQL 语句
     */
    public static String buildCreateTableSQL(SimpleFeatureSource featureSource, String rawTableName, String dbType) throws Exception {
        SimpleFeatureType schema = featureSource.getSchema();

        String tableName = quoteIdentifier(rawTableName, dbType);
        StringBuilder sb = new StringBuilder("CREATE TABLE " + tableName + " (");

        for (AttributeDescriptor attr : schema.getAttributeDescriptors()) {
            String name = attr.getLocalName();
            Class<?> type = attr.getType().getBinding();

            if (Geometry.class.isAssignableFrom(type)) {
                name = "geom"; // 强制命名为 geom（避免复杂几何字段名）
                sb.append(quoteIdentifier(name, dbType)).append(" ").append(getGeometryType(dbType)).append(",");
            } else {
                sb.append(quoteIdentifier(name, dbType)).append(" ").append(toDbType(type, dbType)).append(",");
            }
        }

        sb.setLength(sb.length() - 1); // 去掉最后一个逗号
        sb.append(");");

        return sb.toString();
    }

    /** 获取不同数据库的几何字段类型 */
    private static String getGeometryType(String dbType) {
        switch (dbType.toLowerCase()) {
            case "postgresql": return "geometry";
            case "mysql": return "GEOMETRY";
            case "dm": return "ST_GEOMETRY";
            default: return "geometry";
        }
    }

    /** Java类型映射为SQL类型 */
    private static String toDbType(Class<?> javaType, String dbType) {
        if (String.class.equals(javaType)) {
            return "VARCHAR(255)";
        } else if (Integer.class.equals(javaType) || int.class.equals(javaType)) {
            return "INT";
        } else if (Long.class.equals(javaType) || long.class.equals(javaType)) {
            return "BIGINT";
        } else if (Double.class.equals(javaType) || double.class.equals(javaType)) {
            return "DOUBLE";
        } else if (Float.class.equals(javaType) || float.class.equals(javaType)) {
            return "FLOAT";
        }
        else {
            return "VARCHAR(255)";
        }
    }

    /** 为不同数据库加上字段或表名引号 */
    private static String quoteIdentifier(String name, String dbType) {
        switch (dbType.toLowerCase()) {
            case "mysql":
                return "`" + name + "`";
            case "postgresql":
            case "dm":
                return "\"" + name + "\"";
            default:
                return name;
        }
    }
    /**
     * 根据 SHP 要素生成多条 INSERT SQL 语句（每条一条）
     *
     * @param featureSource GeoTools 的 FeatureSource
     * @param tableName     要插入的数据库表名
     * @param dbType        数据库类型（mysql/postgresql/dm）
     * @return INSERT SQL 列表
     */
    public static List<String> buildInsertSQL(SimpleFeatureSource featureSource, String tableName, String dbType) throws Exception {
        List<String> sqlList = new ArrayList<>();

        // 要素集合
        SimpleFeatureCollection featureCollection = featureSource.getFeatures();
        SimpleFeatureIterator features = featureCollection.features();

        try {
            while (features.hasNext()) {
                SimpleFeature feature = features.next();
                StringBuilder fieldBuilder = new StringBuilder();
                StringBuilder valueBuilder = new StringBuilder();

                for (Property property : feature.getProperties()) {
                    String fieldName = property.getName().toString();
                    Object value = property.getValue();
                    // 统一几何字段名为 geom
                    if (value instanceof Geometry) {
                        fieldName = "geom";
                    }
                    // 字段名加引号（按数据库类型）
                    fieldBuilder.append(quoteIdentifier(fieldName, dbType)).append(",");

                    // 几何字段
                    if (value instanceof Geometry) {
                        Geometry geom = (Geometry) value;
                        String wkt = geom.toText();
                        switch (dbType.toLowerCase()) {
                            case "postgresql":
                                valueBuilder.append("ST_GeomFromText('").append(wkt).append("', 4326)").append(",");
                                break;
                            case "dm":
                                valueBuilder.append("dmgeo.ST_GeomFromText('").append(wkt).append("', 4326)").append(",");
                                break;
                            case "mysql":
                                valueBuilder.append("ST_GeomFromText('").append(wkt).append("')").append(",");
                                break;
                            default:
                                valueBuilder.append("'").append(wkt).append("',");
                                break;
                        }
                    } else if (value == null) {
                        valueBuilder.append("NULL").append(",");
                    } else if (value instanceof Number) {
                        valueBuilder.append(value).append(",");
                    } else {
                        valueBuilder.append("'").append(value.toString().replace("'", "''")).append("',");
                    }
                }

                // 去掉末尾逗号
                fieldBuilder.setLength(fieldBuilder.length() - 1);
                valueBuilder.setLength(valueBuilder.length() - 1);

                // 拼接 SQL
                String sql = String.format("INSERT INTO %s (%s) VALUES (%s);",
                        quoteIdentifier(tableName, dbType),
                        fieldBuilder,
                        valueBuilder);

                sqlList.add(sql);
            }
        } finally {
            features.close();
        }

        return sqlList;
    }

}
